import pymysql as sql
from flask import jsonify

connect = sql.Connect(
    host='rm-bp131vjnd9b99vmuq2o.mysql.rds.aliyuncs.com',
    port=3306,
    user='lin_432718',
    passwd='Lin817818',
    db='rail_traffic',
    charset='utf8'
)
cursor = connect.cursor()


def hello():
    print('line operation')


def getAllLine():
    print('return all lines')

    sql = "select line_id,line_name,department_name,department_id,line_station_id from line,department " \
          "where line.attr_depart_id=department.department_id"
    print(sql)
    cursor.execute(sql)
    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有站点")
        return 0
    else:
        print("查询成功")
        return temp


# 获取线路经过的所有站点，并分为两个列表，前半条线路正序，后半条倒序，用于前台展示
def getstations(station_id_list):
    if not station_id_list:
        return 0
    else:
        stationlist = []
        stationlist1 = []
        stationlist2 = []

        index = 0.5 * len(station_id_list)
        print(index)
        station_id_list1 = station_id_list[0:int(index)]
        print(station_id_list1)
        station_id_list2 = station_id_list[int(index):len(station_id_list)]
        print(station_id_list2)

        for station_id in station_id_list1:
            sql = "select station_id,station_name from station where station_id='" + station_id + "'"
            print(sql)
            cursor.execute(sql)
            temp = cursor.fetchall()
            print(temp)
            stationlist1.append({"station_id": temp[0][0], "station_name": temp[0][1]})
        stationlist.append(stationlist1)

        for station_id in list(reversed(station_id_list2)):
            sql = "select station_id,station_name from station where station_id='" + station_id + "'"
            print(sql)
            cursor.execute(sql)
            temp = cursor.fetchall()
            print(temp)
            stationlist2.append({"station_id": temp[0][0], "station_name": temp[0][1]})
        stationlist.append(stationlist2)
        print(stationlist)
        return stationlist


def newline(line_id, line_name, attr_depart_id, stations):
    print('insert a new line')
    print(stations)
    line_station_id_temp = ""
    for station in stations:
        line_station_id_temp = line_station_id_temp + station + "#"
    line_station_id = line_station_id_temp[0:len(line_station_id_temp) - 1]
    print(line_station_id)

    sql = "insert into line VALUES('" + line_id + "','" + line_name + "','" + attr_depart_id + "','" + line_station_id + "')"
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def getnewnumber():
    sql = "select max(station_id) from station"
    cursor.execute(sql)
    data = cursor.fetchone()
    print(data)
    number = int(data[0]) + 1
    print(number)
    str = "%03d" % number
    print(str)

    return str


def editline(line_id, line_name, attr_depart_id, station_id_list):
    print('edit a line')

    line_station_id_temp = ""
    for station in station_id_list:
        line_station_id_temp = line_station_id_temp + station + "#"
    line_station_id = line_station_id_temp[0:len(line_station_id_temp) - 1]

    sql = "update line set line_name='" + line_name + "',attr_depart_id='" + attr_depart_id + \
          "',line_station_id='" + line_station_id + "' where line_id=" + line_id
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def searchLine(word):
    sql = "select line_id,line_name,department_name,line_station_id from line,department " \
          "where line.line_name like '%" + word + "%' and line.attr_depart_id=department.department_id"
    print(sql)
    cursor.execute(sql)

    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有符合条件的站点")
        return 0
    else:
        print("搜索成功")
        return temp


def deleteLine(word):
    sql = "Delete FROM line WHERE line_id ='" + str(word) + "'"
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def getOptions():
    print('return all station')

    sql = "SELECT station_name,station_id from station"
    print(sql)
    cursor.execute(sql)
    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有站点")
        return 0
    else:
        print("查询成功")
        return temp


def getdepartOptions():
    print('return department to choose')

    # 选择一级部门
    sql = "SELECT  department_id,department_name from department where department_id not in " \
          "(select department_id from department where department_id like '%-%')"
    print(sql)
    cursor.execute(sql)
    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有部门")
        return 0
    else:
        print("查询成功")
        return temp
